<!DOCTYPE html>
<html lang="en" itemscope itemtype="https://schema.org/WebPage">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Vitess / Schema Management</title>

        <!-- Webfont -->
    <link href='http://fonts.googleapis.com/css?family=Roboto:400,100,100italic,300,300italic,400italic,500,500italic,700,700italic,900,900italic' rel='stylesheet' type='text/css'>
    
    <!--<link rel="shortcut icon" type="image/png" href="/favicon.png">-->

    <!-- Bootstrap -->
    <link href="/libs/bootstrap/css/bootstrap.min.css" rel="stylesheet">

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->

    <!-- Styles -->
    <link rel="stylesheet" type="text/css" href="/css/site.css" />
    <!-- Font Awesome icons -->
    <link href="/libs/font-awesome-4.4.0/css/font-awesome.min.css"
          rel="stylesheet"
          type="text/css">
    
    <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
    <!-- Include all compiled plugins (below), or include individual files as needed -->
    <script src="/libs/bootstrap/js/bootstrap.min.js"></script>


    <!-- metadata -->
    <meta name="og:title" content="Vitess / Schema Management"/>
    <meta name="og:image" content="/images/vitess_logo_with_border.svg"/>
    <meta name="og:description" content="Vitess is a database clustering system for horizontal scaling of MySQL."/>

    <link rel="icon" href="/images/vitess_logo_icon_size.png" type="image/png">

    
    <script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');
  ga('create', 'UA-60219601-1', 'auto');
  ga('send', 'pageview');
</script>

    
  </head>
  <body class="docs" id="top_of_page">

    <span id="toc-depth" data-toc-depth="h2,h3"></span>


    <nav id="common-nav" class="navbar navbar-fixed-top inverse">
  <div class="container">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar-collapse-1">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      <a class="navbar-brand" href="/">
        <img class="logo" src="/images/vitess_logo_with_border.svg" alt="Vitess">
      </a>
    </div>

    <!-- Collect the nav links, forms, and other content for toggling -->
    <div class="collapse navbar-collapse" id="navbar-collapse-1">
      <ul class="nav navbar-nav navbar-right" id="standard-menu-links">
        <li><a href="/overview/">Overview</a></li>
        <li><a href="/user-guide/introduction.html">Guides</a></li>
        <li><a href="/reference/vitess-api.html">Reference</a></li>
        <li><a href="http://blog.vitess.io">Blog</a></li>
        <li><a href="https://github.com/youtube/vitess"><i class="fa fa-github"></i> GitHub</a></li>
        <!-- Hide link to blog unless we have actual posts -->
        <!-- <li><a href="/blog/" title="">Blog</a></li> -->
      </ul>
      <ul class="nav nav-stacked mobile-left-nav-menu" id="collapsed-left-menu">
                <li class="submenu">
          <h4 class="arrow-r no-top-margin">Overview</h4>
          <ul style="display: none">
            <li><a href="/overview/">What is Vitess</a></li>
            <li><a href="/overview/scaling-mysql.html">Scaling MySQL with Vitess</a></li>
            <li><a href="/overview/concepts.html">Key Concepts</a></li>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Getting Started</h4>
          <ul style="display: none">
            <li style="padding-bottom: 0"><a href="/getting-started/">Run Vitess on Kubernetes</a>
              <ul style="display: block">
                <li style="padding-bottom: 0"><a href="/getting-started/docker-build.html">Custom Docker Build</a></li>
              </ul>
            </li>
            <li><a href="/getting-started/local-instance.html">Run Vitess Locally</a></li>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">User Guide</h4>
          <ul style="display: none">
            <li><a href="/user-guide/introduction.html">Introduction</a>
            <li><a href="/user-guide/client-libraries.html">Client Libraries</a>
            <li><a href="/user-guide/backup-and-restore.html">Backing Up Data</a>
            <li><a href="/user-guide/reparenting.html">Reparenting</a></li>
            <li style="padding-bottom: 0"><a href="/user-guide/schema-management.html">Schema Management</a></li>
              <ul style="display: block">
                <li style="padding-bottom: 0"><a href="/user-guide/vschema.html">VSchema Guide</a></li>
                <li style="padding-bottom: 0"><a href="/user-guide/schema-swap.html">Schema Swap (Tutorial)</a></li>
              </ul>
            <li style="padding-bottom: 0"><a href="/user-guide/sharding.html">Sharding</a>
              <ul style="display: block">
                <li><a href="/user-guide/horizontal-sharding-workflow.html">Horizontal Sharding (Tutorial, automated)</a></li>
                <li><a href="/user-guide/horizontal-sharding.html">Horizontal Sharding (Tutorial, manual)</a></li>
                <li><a href="/user-guide/sharding-kubernetes-workflow.html">Sharding in Kubernetes (Tutorial, automated)</a></li>
                <li style="padding-bottom: 0"><a href="/user-guide/sharding-kubernetes.html">Sharding in Kubernetes (Tutorial, manual)</a></li>
              </ul>
            </li>
            <li><a href="/user-guide/vitess-sequences.html">Vitess Sequences</a></li>
            <li><a href="/user-guide/mysql-server-protocol.html">MySQL Server Protocol</a></li>
            <li><a href="/user-guide/vitess-replication.html">Vitess and Replication</a></li>
            <li><a href="/user-guide/update-stream.html">Update Stream</a></li>
            <li><a href="/user-guide/row-based-replication.html">Row Based Replication</a></li>
            <li><a href="/user-guide/topology-service.html">Topology Service</a></li>
            <li><a href="/user-guide/transport-security-model.html">Transport Security Model</a></li>
            <li style="padding-bottom: 0"><a href="/user-guide/launching.html">Launching</a>
              <ul style="display: block">
                <li><a href="/user-guide/scalability-philosophy.html">Scalability Philosophy</a></li>
                <li><a href="/user-guide/production-planning.html">Production Planning</a></li>
                <li><a href="/user-guide/server-configuration.html">Server Configuration</a></li>
                <li><a href="/user-guide/twopc.html">2PC Guide</a></li>
                <li style="padding-bottom: 0"><a href="/user-guide/troubleshooting.html">Troubleshooting</a></li>
              </ul>
            </li>
            <li><a href="/user-guide/upgrading.html">Upgrading</a></li>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Reference Guides</h4>
          <ul style="display: none">
            <li><a href="/reference/vitess-api.html">Vitess API</a>
            <li><a href="/reference/vtctl.html">vtctl Commands</a>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Other Resources</h4>
          <ul style="display: none">
            <li><a href="/resources/presentations.html">Presentations</a>
            <li><a href="http://blog.vitess.io/">Blog</a>
            <li><a href="/resources/roadmap.html">Roadmap</a>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Contributing</h4>
          <ul style="display: none">
            <li><a href="/contributing/">Contributing to Vitess</a>
            <li><a href="/contributing/github-workflow.html">GitHub Workflow</a>
            <li><a href="/contributing/code-reviews.html">Code Reviews</a>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Internal</h4>
          <ul style="display: none">
            <li><a href="/internal/">Overview</a>
            <li><a href="/internal/release-instructions.html">Release Instructions</a>
            <li><a href="/internal/publish-website.html">Publish Website</a>
          </ul>
        </li>

        <div>
          <form method="get" action="/search/">
            <input id="search-form" name="q" type="text" placeholder="Search">
          </form>
        </div>

        <li><a href="https://github.com/youtube/vitess" id="collapsed-left-menu-repo-link"><i class="fa fa-github"></i> GitHub</a></li>
      </ul>
    </div><!-- /.navbar-collapse -->
  </div><!-- /.container-fluid -->
</nav>

    
<div id="masthead">
  <div class="container">
    <div class="row">
      <div class="col-md-9">
        <h1>Schema Management</h1>
      </div>
    </div>
  </div>
</div>


<div class="container">
    <div class="row scroll-margin" id="toc-content-row">
    <div class="col-md-2" id="leftCol">
      <ul class="nav nav-stacked mobile-left-nav-menu" id="sidebar">
                <li class="submenu">
          <h4 class="arrow-r no-top-margin">Overview</h4>
          <ul style="display: none">
            <li><a href="/overview/">What is Vitess</a></li>
            <li><a href="/overview/scaling-mysql.html">Scaling MySQL with Vitess</a></li>
            <li><a href="/overview/concepts.html">Key Concepts</a></li>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Getting Started</h4>
          <ul style="display: none">
            <li style="padding-bottom: 0"><a href="/getting-started/">Run Vitess on Kubernetes</a>
              <ul style="display: block">
                <li style="padding-bottom: 0"><a href="/getting-started/docker-build.html">Custom Docker Build</a></li>
              </ul>
            </li>
            <li><a href="/getting-started/local-instance.html">Run Vitess Locally</a></li>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">User Guide</h4>
          <ul style="display: none">
            <li><a href="/user-guide/introduction.html">Introduction</a>
            <li><a href="/user-guide/client-libraries.html">Client Libraries</a>
            <li><a href="/user-guide/backup-and-restore.html">Backing Up Data</a>
            <li><a href="/user-guide/reparenting.html">Reparenting</a></li>
            <li style="padding-bottom: 0"><a href="/user-guide/schema-management.html">Schema Management</a></li>
              <ul style="display: block">
                <li style="padding-bottom: 0"><a href="/user-guide/vschema.html">VSchema Guide</a></li>
                <li style="padding-bottom: 0"><a href="/user-guide/schema-swap.html">Schema Swap (Tutorial)</a></li>
              </ul>
            <li style="padding-bottom: 0"><a href="/user-guide/sharding.html">Sharding</a>
              <ul style="display: block">
                <li><a href="/user-guide/horizontal-sharding-workflow.html">Horizontal Sharding (Tutorial, automated)</a></li>
                <li><a href="/user-guide/horizontal-sharding.html">Horizontal Sharding (Tutorial, manual)</a></li>
                <li><a href="/user-guide/sharding-kubernetes-workflow.html">Sharding in Kubernetes (Tutorial, automated)</a></li>
                <li style="padding-bottom: 0"><a href="/user-guide/sharding-kubernetes.html">Sharding in Kubernetes (Tutorial, manual)</a></li>
              </ul>
            </li>
            <li><a href="/user-guide/vitess-sequences.html">Vitess Sequences</a></li>
            <li><a href="/user-guide/mysql-server-protocol.html">MySQL Server Protocol</a></li>
            <li><a href="/user-guide/vitess-replication.html">Vitess and Replication</a></li>
            <li><a href="/user-guide/update-stream.html">Update Stream</a></li>
            <li><a href="/user-guide/row-based-replication.html">Row Based Replication</a></li>
            <li><a href="/user-guide/topology-service.html">Topology Service</a></li>
            <li><a href="/user-guide/transport-security-model.html">Transport Security Model</a></li>
            <li style="padding-bottom: 0"><a href="/user-guide/launching.html">Launching</a>
              <ul style="display: block">
                <li><a href="/user-guide/scalability-philosophy.html">Scalability Philosophy</a></li>
                <li><a href="/user-guide/production-planning.html">Production Planning</a></li>
                <li><a href="/user-guide/server-configuration.html">Server Configuration</a></li>
                <li><a href="/user-guide/twopc.html">2PC Guide</a></li>
                <li style="padding-bottom: 0"><a href="/user-guide/troubleshooting.html">Troubleshooting</a></li>
              </ul>
            </li>
            <li><a href="/user-guide/upgrading.html">Upgrading</a></li>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Reference Guides</h4>
          <ul style="display: none">
            <li><a href="/reference/vitess-api.html">Vitess API</a>
            <li><a href="/reference/vtctl.html">vtctl Commands</a>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Other Resources</h4>
          <ul style="display: none">
            <li><a href="/resources/presentations.html">Presentations</a>
            <li><a href="http://blog.vitess.io/">Blog</a>
            <li><a href="/resources/roadmap.html">Roadmap</a>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Contributing</h4>
          <ul style="display: none">
            <li><a href="/contributing/">Contributing to Vitess</a>
            <li><a href="/contributing/github-workflow.html">GitHub Workflow</a>
            <li><a href="/contributing/code-reviews.html">Code Reviews</a>
          </ul>
        </li>
        <li class="submenu">
          <h4 class="arrow-r">Internal</h4>
          <ul style="display: none">
            <li><a href="/internal/">Overview</a>
            <li><a href="/internal/release-instructions.html">Release Instructions</a>
            <li><a href="/internal/publish-website.html">Publish Website</a>
          </ul>
        </li>

        <div>
          <form method="get" action="/search/">
            <input id="search-form" name="q" type="text" placeholder="Search">
          </form>
        </div>

      </ul>
    </div>
    <div class="col-md-3" id="rightCol">
      <div class="nav nav-stacked" id="tocSidebar">
        <div id="toc"></div>
      </div>
    </div>
    <div class="col-md-7" id="centerCol">
      <div id="centerTextCol">
        <p>Your MySQL database schema lists the tables in your database and
contains table definitions that explain how to create those tables.
Table definitions identify table names, column names, column types,
primary key information, and so forth.</p>

<p>This document describes the <code><a href="/reference/vtctl.html">vtctl</a></code>
commands that you can use to <a href="#reviewing-your-schema">review</a> or
<a href="#changing-your-schema">update</a> your schema in Vitess.</p>

<p>Note that this functionality is not recommended for long-running schema changes. In such cases, we recommend to do a <a href="/user-guide/schema-swap.html">schema swap</a> instead.</p>

<h2 id="reviewing-your-schema">Reviewing your schema</h2>

<p>This section describes the following <code>vtctl</code> commands, which let you look at the schema and validate its consistency across tablets or shards:</p>

<ul>
<li><a href="#getschema">GetSchema</a></li>
<li><a href="#validateschemashard">ValidateSchemaShard</a></li>
<li><a href="#validateschemakeyspace">ValidateSchemaKeyspace</a></li>
</ul>

<h3 id="getschema">GetSchema</h3>

<p>The <code><a href="/reference/vtctl.html#getschema">GetSchema</a></code> command
displays the full schema for a tablet or a subset of the tablet&#39;s tables.
When you call <code>GetSchema</code>, you specify the tablet alias that
uniquely identifies the tablet. The <code>&lt;tablet alias&gt;</code>
argument value has the format <code>&lt;cell name&gt;-&lt;uid&gt;</code>.</p>

<p><strong>Note:</strong> You can use the
<code><a href="/reference/vtctl.html#listalltablets">vtctl ListAllTablets</a></code> 
command to retrieve a list of tablets in a cell and their unique IDs.</p>

<p>The following example retrieves the schema for the tablet with the
unique ID <code>test-000000100</code>:</p>
<div class="highlight"><pre><code class="language-text" data-lang="text">GetSchema test-000000100
</code></pre></div>
<h3 id="validateschemashard">ValidateSchemaShard</h3>

<p>The
<code><a href="/reference/vtctl.html#validateschemashard">ValidateSchemaShard</a></code>
command confirms that for a given keyspace, all of the slave tablets
in a specified shard have the same schema as the master tablet in that
shard. When you call <code>ValidateSchemaShard</code>, you specify both 
the keyspace and the shard that you are validating.</p>

<p>The following command confirms that the master and slave tablets in
shard <code>0</code> all have the same schema for the <code>user</code>
keyspace:</p>
<div class="highlight"><pre><code class="language-text" data-lang="text">ValidateSchemaShard user/0
</code></pre></div>
<h3 id="validateschemakeyspace">ValidateSchemaKeyspace</h3>

<p>The <code><a href="/reference/vtctl.html#validateschemakeyspace">ValidateSchemaKeyspace</a></code>
command confirms that all of the tablets in a given keyspace have
the the same schema as the master tablet on shard <code>0</code>
in that keyspace. Thus, whereas the <code>ValidateSchemaShard</code>
command confirms the consistency of the schema on tablets within a shard
for a given keyspace, <code>ValidateSchemaKeyspace</code> confirms the
consistency across all tablets in all shards for that keyspace.</p>

<p>The following command confirms that all tablets in all shards have the
same schema as the master tablet in shard <code>0</code> for the
<code>user</code> keyspace:</p>
<div class="highlight"><pre><code class="language-text" data-lang="text">ValidateSchemaKeyspace user
</code></pre></div>
<h2 id="changing-your-schema">Changing your schema</h2>

<p>This section describes the <code>vtctl ApplySchema</code> command, which
supports schema modifications. Vitess&#39; schema modification functionality
is designed the following goals in mind:</p>

<ul>
<li>Enable simple updates that propagate to your entire fleet of servers.</li>
<li>Require minimal human interaction.</li>
<li>Minimize errors by testing changes against a temporary database.</li>
<li>Guarantee very little downtime (or no downtime) for most schema updates.</li>
<li>Do not store permanent schema data in the topology server.</li>
</ul>

<p>Note that, at this time, Vitess only supports
<a href="https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-definition.html">data definition statements</a>
that create, modify, or delete database tables.
For instance, <code>ApplySchema</code> does not affect stored procedures
or grants.</p>

<h3 id="applyschema">ApplySchema</h3>

<p>The <code><a href="/reference/vtctl.html#applyschema">ApplySchema</a></code>
command applies a schema change to the specified keyspace on every
master tablet, running in parallel on all shards. Changes are then
propagated to slaves via replication. The command format is:
<code class="prettyprint">
ApplySchema {-sql=&lt;sql&gt; || -sql_file=&lt;filename&gt;} &lt;keyspace&gt;
</code></p>

<p>When the <code>ApplySchema</code> action actually applies a schema
change to the specified keyspace, it performs the following steps:</p>

<ol>
<li>It finds shards that belong to the keyspace, including newly added
shards if a <a href="/user-guide/sharding.html#resharding">resharding event</a>
has taken place.</li>
<li>It validates the SQL syntax and determines the impact of the schema
change. If the scope of the change is too large, Vitess rejects it.
See the <a href="#permitted-schema-changes">permitted schema changes</a> section
for more detail.</li>
<li>It employs a pre-flight check to ensure that a schema update will
succeed before the change is actually applied to the live database.
In this stage, Vitess copies the current schema into a temporary
database, applies the change there to validate it, and retrieves
the resulting schema. By doing so, Vitess verifies that the change
succeeds without actually touching live database tables.</li>
<li>It applies the SQL command on the master tablet in each shard.</li>
</ol>

<p>The following sample command applies the SQL in the <strong>user_table.sql</strong>
file to the <strong>user</strong> keyspace:</p>
<div class="highlight"><pre><code class="language-text" data-lang="text">ApplySchema -sql_file=user_table.sql user
</code></pre></div>
<h4 id="permitted-schema-changes">Permitted schema changes</h4>

<p>The <code>ApplySchema</code> command supports a limited set of DDL
statements. In addition, Vitess rejects some schema changes because
large changes can slow replication and may reduce the availability
of your overall system.</p>

<p>The following list identifies types of DDL statements that Vitess
supports:</p>

<ul>
<li><code>CREATE TABLE</code></li>
<li><code>CREATE INDEX</code></li>
<li><code>CREATE VIEW</code></li>
<li><code>ALTER TABLE</code></li>
<li><code>ALTER VIEW</code></li>
<li><code>RENAME TABLE</code></li>
<li><code>DROP TABLE</code></li>
<li><code>DROP INDEX</code></li>
<li><code>DROP VIEW</code></li>
</ul>

<p>In addition, Vitess applies the following rules when assessing the
impact of a potential change:</p>

<ul>
<li><code>DROP</code> statements are always allowed, regardless of the
table&#39;s size.</li>
<li><code>ALTER</code> statements are only allowed if the table on the
shard&#39;s master tablet has 100,000 rows or less.</li>
<li>For all other statements, the table on the shard&#39;s master tablet
must have 2 million rows or less.</li>
</ul>

<p>If a schema change gets rejected because it affects too many rows, you can specify the flag <code class="prettyprint">-allow_long_unavailability</code> to tell <code class="prettyprint">ApplySchema</code> to skip this check.
However, we do not recommend this. Instead, you should apply large schema changes by following the <a href="/user-guide/schema-swap.html">schema swap process</a>.</p>

      </div>
    </div>
  </div>

</div>

    <div class="page-spacer"></div>
    <footer role="contentinfo" id="site-footer">
  <nav role="navigation" class="menu bottom-menu">
    
    <a href="https://groups.google.com/forum/#!forum/vitess" target="_blank">Contact: vitess@googlegroups.com</a>&nbsp;&nbsp;<b>·</b>&nbsp;&nbsp;
    <a href="https://groups.google.com/forum/#!forum/vitess-announce" target="_blank">Announcements</a>&nbsp;&nbsp;<b>·</b>&nbsp;&nbsp;
    &#169; 2017 <a href="/">Vitess</a> powered by <a href="https://developers.google.com/open-source/">Google Inc</a>
  </nav>
</footer>


        <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<!--    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>   -->
    <!-- Include all compiled plugins (below), or include individual files as needed -->
<!--
    <script src="/libs/bootstrap/js/bootstrap.min.js"></script>
-->
    <!-- Include the common site javascript -->
    <script src="/js/common.js" type="text/javascript" charset="utf-8"></script>


  </body>
</html>
